package com.zx.oa.dao.morningResult;

import com.zx.oa.dao.BaseDao;
import com.zx.oa.entity.ClassInfo;
import com.zx.oa.entity.Result;
import com.zx.oa.entity.WeekResult;

import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.SimpleTimeZone;

public class MorningResultDaoImpl extends BaseDao implements MorningResultDao {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    @Override
    public int getMorningResultTotalCount(String major, String className, String studentName, String date, String result) {
        conn = this.getConnection();
        List<Object> parsms = new ArrayList<>();

        StringBuffer sql = new StringBuffer("SELECT count(*) \n" +
                "FROM ztestudent s \n" +
                "INNER JOIN zteresult r ON r.`studentid` = s.`studentId`\n" +
                "INNER JOIN zteclassinfo c ON s.`classId` = c.`classId`\n" +
                "INNER JOIN ztemajor m ON m.`id` = c.`studyType`\n" +
                "WHERE 1=1 AND r.examType=1\n");
        if (major != null && !"".equals(major)) {
            sql.append(" AND m.major = ?");
            parsms.add(major);
        }
        if (className != null && !"".equals(className)) {
            sql.append(" AND c.className like ?");
            parsms.add(className + "%");
        }
        if (studentName != null && !"".equals(studentName)) {
            sql.append(" AND s.studentName like ?");
            parsms.add(studentName + "%");
        }
        if (date != null && !"".equals(date)) {
            sql.append(" AND r.examdate like ?");
            parsms.add(date + "%");
        }
        // restlt 需要传一个 60 and 80 这样的字符串
        if (result != null && !"".equals(result)) {
            sql.append(" AND r.studentresult > ?");
            parsms.add(result);
        }
        //记录条数
        int count = 0;

        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql + "");
            //依次给win号传值
            for (int i = 0; i < parsms.size(); i++) {
                pstmt.setObject(i+1,parsms.get(i));
            }
            rs = pstmt.executeQuery();
            while(rs.next()){
                count = rs.getInt(1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return count;
    }


    @Override
    public List<WeekResult> getMorningResultList(String major, String className, String studentName, String date, String result, int currentPageNo, int pageSize) {
        List<WeekResult> weekResultList = new ArrayList<>();
        conn = this.getConnection();
        List<Object> parsms = new ArrayList<>();

        StringBuffer sql = new StringBuffer("SELECT r.Id,s.studentName,r.examdate,r.examType,r.studentresult " +
                "FROM ztestudent s " +
                "INNER JOIN zteresult r ON r.`studentid` = s.`studentId`" +
                "INNER JOIN zteclassinfo c ON s.`classId` = c.`classId`" +
                "INNER JOIN ztemajor m ON m.`id` = c.`studyType`" +
                "WHERE 1=1 AND r.examType=1");
        if (major != null && !"".equals(major)) {
            sql.append(" AND m.major = ?");
            parsms.add(major);
        }
        if (className != null && !"".equals(className)) {
            sql.append(" AND c.className like ?");
            parsms.add(className + "%");
        }
        if (studentName != null && !"".equals(studentName)) {
            sql.append(" AND s.studentName like ?");
            parsms.add(studentName + "%");
        }
        if (date != null && !"".equals(date)) {
            sql.append(" AND r.examdate like ?");
            parsms.add(date + "%");
        }
        // restlt 需要传一个 60 and 80 这样的字符串
        if (result != null && !"".equals(result)) {
            sql.append(" AND r.studentresult between ?");
            parsms.add(result);
        }
        sql.append(" LIMIT ?,? ");
        parsms.add((currentPageNo-1)*pageSize);
        parsms.add(pageSize);
        //根据条件查询到数据
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            String rExamType=null;
            //依次给win号传值
            for (int i = 0; i < parsms.size(); i++) {
                pstmt.setObject(i+1,parsms.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int stuId = rs.getInt("id");
                String rName = rs.getString("studentName");
                Date rDate = rs.getDate("examdate");
                int anInt = rs.getInt("examType");
                String rResrlt = rs.getString("studentresult");
                WeekResult weekResult = new WeekResult(stuId,rName,rDate,"晨考",rResrlt);
                weekResultList.add(weekResult);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return weekResultList;
    }

    @Override
    public boolean deleteResult(int id) {
        String sql = "DELETE FROM zteresult WHERE Id=?";
        Object[] params = {id};

        if (this.executeUpdateInfo(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public List<WeekResult> getMonthResultList(String emajor, String eclassname, String month) {
        conn = this.getConnection();
        List<WeekResult> weekResultList =new ArrayList<>();
        return weekResultList;
    }

    @Override
    public Result getMorningResul(String id) {
        Result result = null;
        SimpleDateFormat simpleDateFormat =  new SimpleDateFormat("yyyy-MM-dd");
        ClassInfo classInfo = null;
        String sb = "SELECT * FROM zteresult   WHERE id=?";

        conn = this.getConnection();
        try {
            pstmt = conn.prepareStatement(sb);
            pstmt.setObject(1,id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int Id = rs.getInt("id");
                int studentid = rs.getInt("studentid");
                String examdate =simpleDateFormat.format( rs.getDate("examdate"));
                int examType = rs.getInt("examType");
                String studenteresult = rs.getString("studenteresult");
                 result = new Result(Id,studentid,examdate,examType,studenteresult);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn, pstmt, rs);
        }
        return result;
    }

    @Override
    public boolean modifyResult(int id, String date, String result) {
        try {
            conn = getConnection();
            String sql = "UPDATE zteresult SET examdate = ?,studentresult = ? WHERE id = ?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,date);
            pstmt.setString(2,result);
            pstmt.setInt(3,id);

            int i = pstmt.executeUpdate();
            if (i == 1){
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }

        return false;
    }
}
